The development of the EDVAC computer system of 1948 is often cited as the beginning of the computer era. Since that time, computer systems have evolved into extremely sophisticated devices, and computer systems may be found in many different settings. Computer systems typically include a combination of hardware, such as semiconductors and circuit boards, and software, also known as computer programs.
Fundamentally, computer systems are used for the storage, manipulation, and analysis of data, which may be anything from complicated financial information to simple baking recipes. It is no surprise, then, that the overall value or worth of a computer system depends largely upon how well the computer system stores, manipulates, and analyzes data. One mechanism for managing data is called a Data Management System (DMS), which may also be called a database system or simply a database.
At the most basic level, a database stores data as series of logical tables. Each table is made up of rows and columns. Each table has a unique name within the database and each column has a unique name within the particular table. Different statements called queries allow the user or an application program to obtain data from the database. As one might imagine, queries range from being very simple to very complex. When a database receives a query, the database interprets the query and determines what internal steps are necessary to satisfy the query. These internal steps may include identification of the table or tables specified in the query, the row or rows selected in the query, and other information such as whether to use an existing index, whether to build a temporary index, whether to use a temporary file to execute a sort, and/or the order in which the tables are to be joined together to satisfy the query. When taken together, these internal steps are referred to as an execution plan or an access plan. The access plan is typically created by a software component that is often called a query optimizer. When a query optimizer creates an access plan for a given query, the access plan is often saved by the data management system in the program object, e.g., the application program, that requested the query. The access plan may also be saved in an SQL (Structured Query Language) package or an access plan cache. Then, when the user or program object repeats the query, the database can reutilize the saved access plan instead of undergoing the expensive and time-consuming process of recreating it.
Despite the existence of a saved access plan in the program object, some triggers, events, or conditions may cause the query optimizer to rebuild the access plan. Often the rebuilt access plan turns out to be the same as the saved access plan, so the rebuilding process was actually unnecessary. Unfortunately, no way exists to determine prior to the rebuilding that the resultant rebuilt access plan will turn out to be identical to the saved access plan.
The problem with the rebuilding turning out to be unnecessary is exacerbated when multiple jobs call the same program object at about the same time, and each is trying to update its access plan. For example, suppose that one job detects a trigger that causes it to rebuild the saved access plan, but then the other jobs see the same trigger, which causes all the jobs to rebuild the saved access plan. But none of the jobs can save their rebuilt access plan back into the program object because all the other jobs hold read locks on the saved access plan, and they all need an exclusive lock to update the saved access plan. Thus, the end result is that all jobs may continue to rebuild the access plan for some time since none of them can save the rebuilt access plan back into the program object due to contention, yet as previously stated, the rebuilding is often unnecessary, so all of the jobs are rebuilding the access plan in vain.
Without a better way to handle rebuilding access plans, computers will continue to suffer from performance problems caused by unnecessary rebuilding.